The present invention relates to a system and method for managing a database table, and more particularly relates to a system and method for using a specialized query format to maintain a record of a user""s state within the database table and to accommodate for null values within the table.
One of the primary benefits provided by the Internet is widespread access to data maintained by data providers in centralized databases. For example, a wholesaler of retail goods may provide retail outlets access to its inventory database or a public library may provide borrowers access to its online card catalog. Users at the retail outlets can access the database to determine product attributes such as product descriptions, product availability, and product pricing. Library borrowers could determine the availability of books. Such databases can be very large, which presents obstacles to effective data distribution. With large databases, a policy decision must be made by the data provider as to how to deliver data to the user over the Internet.
One approach to providing data to a user is by allowing the user to download the entire contents of the database to the user""s local computer. However, this approach has various problems. One problem is that the user must have system resources capable of processing the large database with acceptable performance. Some large databases are simply too large to permit the user to use the database with ease and efficiency and may ultimately deter users from exploiting the benefits of the database. Another problem with this approach is that the user may not have a tool for effectively browsing the database after it has been downloaded. In this case, the database becomes a burdensome and unwieldy mass of data reducing its usefulness to the user.
Another common approach is to deliver the data to the user in batches. With this approach, the data provider will partition the data into batches and deliver the batches, one at a time, to the end user. The end user is, thus, provided with a manageable portion of the database. Maintaining a partitioned database can be difficult in applications where the database must be regularly updated or modified. Moreover, in order to permit the user to browse between adjacent batches, the data provider must maintain a record as to which batch the user has accessed. This approach also requires that the data provider maintain records of each user""s activity with respect to the database. That is, the data provider must maintain a record of the user""s xe2x80x9cstatexe2x80x9d or location within the database.
There is a need in the art for a means for providing users with remote access (e.g., via the Internet) that is more efficient and more effective than the above-described existing approaches. The system should provide good end-user performance by delivering batches (i.e., partial data sets) to a user and should provide good server performance by eliminating the necessity to maintain partitioned databases and/or state information on the user. The system should also be capable of being implemented without significant modifications of existing databases and should be able to accommodate null values found in the records of existing databases.
The present invention solves the problems of the prior art by providing a system and method for delivering batches of data to a client, while maintaining the client""s state in the accessed database. The system and method eliminates the need for a web server to maintain the location of any given client within a database. Accordingly, the web server operates with greater efficiency, because it is freed from having to maintain the state of any given client within a database and need not create or maintain partitioned databases.
The typical Internet database searching arrangement involves a database server, a web server and an end user""s computer (client computer). The database server maintains the data to which the end user seeks access. The web server acts an intermediary and permits access to the database server""s database via the internet. The end user""s computer will be running an application, such as a web browser, which will permit the end user to communicate with the web server and query the database on the database server.
The present invention enables a web server to deliver to the client computer a web site with a query entry field. The end user can enter a query in the field and submit the query to the web server. The web server will, in turn, access the database server and return a first batch of records satisfying the search to the end user. The first batch can be configured with a xe2x80x9cnextxe2x80x9d button that will allow the user to access the next group of records from the database. Once the user has received the next group of records from the database, the user will also be provided with a xe2x80x9cpreviousxe2x80x9d button by which the user may access the adjacent, previously delivered batch of records.
The present invention accomplishes batch fetching by implementing a novel Structured Query Language (SQL) statement. The SQL statements reference minimum and maximum reference values from previously received batched of records.
By maintaining minimum and maximum reference values on the client""s computer, the user""s state within the accessed database can be maintained without storing any information on the web server. The next and previous batches are successfully obtained by comparing these stored reference values to the database so that the user can quickly move between adjacent batches of records. Thus, the invention provides a simple and elegant solution to the identified problem.
The present invention is capable of accommodating null values found in the accessed database. Typically, null values are arbitrarily sorted within the defined sorting structure of SQL and other programming models because a null value by definition is neither greater than nor less than any other given value. Thus, the comparison step described above could fail to properly retrieve records that contain null values. As a result of this failure, the user might be unable to properly traverse the database. The present invention solves this problem by using a specialized xe2x80x9cWHERExe2x80x9d clause in a query that specifically accommodates situations where the desired batch of records contains one or more null values, thereby providing for the retrieval of the proper batch of records.
The various aspects of the present invention may be more clearly understood and appreciated from a review of the following detailed description of the disclosed embodiments and by reference to the drawings and claims